package ThreeLayer.DAL;

import ThreeLayer.BLL.SqlC;
import TwoLayer.User;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class UserDA {
    SqlC sqlC = new SqlC();
    public void addUser(User user) throws ClassNotFoundException,SQLException {
        Connection con = sqlC.GetConnect();
        PreparedStatement preparedStatement = null;
        String Sql = "insert into t_user(id,username,address,phone)values(?,?,?,?)";
        preparedStatement = con.prepareStatement(Sql);
        preparedStatement.setInt(1, user.getId());
        preparedStatement.setString(2, user.getUsername());
        preparedStatement.setString(3, user.getAddress());
        preparedStatement.setString(4, user.getPhone());
        preparedStatement.execute();
        preparedStatement.close();
        con.close();
    }
    public void delUser(User user) throws ClassNotFoundException, SQLException{
        Connection con = sqlC.GetConnect();
        PreparedStatement preparedStatement = null;
        String Sql = "DELETE FROM t_user WHERE id = ?";
        preparedStatement = con.prepareStatement(Sql);
        preparedStatement.setInt(1, user.getId());
        preparedStatement.execute();
        preparedStatement.close();
        con.close();
    }
    public void updateUser(User user) throws ClassNotFoundException, SQLException{
        Connection con = sqlC.GetConnect();
        PreparedStatement preparedStatement = null;
        String Sql = "update t_user set username=?,address = ?,phone = ? where id = ?";
        preparedStatement = con.prepareStatement(Sql);
        preparedStatement.setString(1, user.getUsername());
        preparedStatement.setString(2, user.getAddress());
        preparedStatement.setString(3, user.getPhone());
        preparedStatement.setLong(4, user.getId());
        preparedStatement.execute();
        preparedStatement.close();
        con.close();
    }
    public List<User> showUser() throws ClassNotFoundException, SQLException{
        Connection con = sqlC.GetConnect();
        List<User> list = new ArrayList<User>();
        ResultSet rs = null;
        PreparedStatement preparedStatement = null;
        String Sql = "select * from t_user";
        preparedStatement = con.prepareStatement(Sql);
        rs = preparedStatement.executeQuery();//执行数据库的查询，并返回查询结果
        while (rs.next()) {//rs.next,让光标向下移动一次并判断下一个元素是否有值，如果有值则返回真进入循环
            User user = new User();
            user.setId(rs.getInt("id"));//将结果集中当前元素的显示列名为id的数据获取出来并设置到user的id属性上
            user.setUsername(rs.getString("username"));
            user.setAddress(rs.getString("address"));
            user.setPhone(rs.getString("phone"));
            list.add(user);
        }
        preparedStatement.execute();
        preparedStatement.close();
        con.close();
        return list;
    }
}
